﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CustomsSystem.BLL;
using System.Data;
using CustomsSystem.Model;
using Maticsoft.Common;
using Microsoft.Win32;

namespace WEB.T_bill
{
    public partial class monthCustomView : Form.baseFile
    {

        DataTable dt;
        BillManage bll = new BillManage();
        CustomsSystem.BLL.info_formMaster fmbll = new CustomsSystem.BLL.info_formMaster();
        //根据公司名称显示业务
        protected void Page_Load(object sender, EventArgs e)
        {
            this.tbTimestart.Attributes.Add("onClick", "new WdatePicker()");
            this.tbTimestop.Attributes.Add("onClick", "new WdatePicker()");
            if (!IsPostBack)
            {
                //模板类型绑定
                ddl_Model.DataSource = fmbll.GetAllList().Tables[0];
                ddl_Model.DataTextField = "formName";
                ddl_Model.DataValueField = "id";
                ddl_Model.DataBind();

                ddl_Model.Items.Add(new ListItem("票结默认模板", "0"));
                ddl_Model.SelectedIndex = ddl_Model.Items.IndexOf(ddl_Model.Items.FindByValue("0"));//默认选项

                DateTime A = new DateTime();
                A = DateTime.Today;
                tbTimestop.Text = A.ToShortDateString();
                tbTimestart.Text = A.AddDays(-30).ToShortDateString();
            }

        }

        /// <summary>
        /// 获取DATATABLE
        /// </summary>
        /// <param name="sql">查询条件</param>
        /// <param name="strFeetype">费用类型</param>
        /// <param name="strDate">日期</param>
        /// <returns></returns>
        protected DataTable DT(string sql, string strFeetype, string strDate)
        {
            string QueryText = tbQuery.Text.Trim();
            string agencyDate = "";//传入日期，在数据库是agencyDate
            if (QueryText == null || QueryText == "")
            { MessageBox.Show(this, "请填写客户名称！"); }
            else
            {
                ///验证公司
                CustomsSystem.Model.Customer cusmodel = new CustomsSystem.BLL.CustomerManage().GetModel(QueryText);
                if (cusmodel == null)
                { MessageBox.Show(this, "查无该公司，请查看公司名称是否正确！"); }

                else
                {
                    #region 当公司不为空时
                    //查询
                    //sql = " and payedID != 1 and cID = " + cusmodel.ID;
                    string cid = cusmodel.ID.ToString();
                    #region 日期搜索的判断
                    if (tbTimestart.Text.Trim() != "")
                    {
                        //日期截止时间为空的时候，默认为现在的时间
                        if (tbTimestop.Text.Trim() == "")
                        {
                            DateTime timeStop = DateTime.Now;
                            //strDate += " and 日期 between ''" + Convert.ToDateTime(tbTimestart.Text).ToString("yyyy-MM-dd") + "'' and ''" + timeStop + "''";
                            agencyDate += " and agencyDate between '" + Convert.ToDateTime(tbTimestart.Text).ToString("yyyy-MM-dd") + "' and '" + timeStop + "'";
                        }
                        else
                        {
                            //strDate += " and 日期 between ''" + Convert.ToDateTime(tbTimestart.Text).ToString("yyyy-MM-dd") + "'' and ''" + Convert.ToDateTime(tbTimestop.Text).ToString("yyyy-MM-dd") + "''";
                            agencyDate += " and agencyDate between '" + Convert.ToDateTime(tbTimestart.Text).ToString("yyyy-MM-dd") + "' and '" + Convert.ToDateTime(tbTimestop.Text).ToString("yyyy-MM-dd") + "'";
                        }

                    }
                    #endregion
                    strDate += " order by agencyDate " + ddlorder.SelectedValue + " ";
                    //重新绑定数据库
                    CustomsSystem.BLL.BillManage bll = new BillManage();
                    dt = bll.GetPivot(strFeetype, agencyDate, cid, strDate);
                    if (dt.Rows.Count < 1)
                    { //Response.Write("<script>alert('数据为空');windows.history.go(-2);</script>");
                        MessageBox.Show(this, "数据为空");
                    }
                    else
                    {
                        //去除不需要的
                        //dt.Columns.Remove("remark");
                        if (cb1.Checked == false)
                        { dt.Columns.Remove("订单号"); }
                        if (cb2.Checked == false)
                        { dt.Columns.Remove("合同号"); }
                        if (cb3.Checked == false)
                        { dt.Columns.Remove("船名"); }
                        if (cb4.Checked == false)
                        { dt.Columns.Remove("货名"); }
                        if (cb5.Checked == false)
                        { dt.Columns.Remove("目的地"); }
                        if (cb6.Checked == false)
                        { dt.Columns.Remove("数量"); }
                        if (cb7.Checked == false)
                        { dt.Columns.Remove("报关单号"); }
                        if (cb8.Checked == false)
                        { dt.Columns.Remove("报检号"); }
                        if (cb9.Checked == false)
                        { dt.Columns.Remove("提单号"); }
                        if (cbagency.Checked == false)
                        { dt.Columns.Remove("船舶代理"); }
                        if (cbDangerousGoods.Checked == false)
                        { dt.Columns.Remove("危险品"); }
                        if (cbcontainerNo.Checked == false)
                        { dt.Columns.Remove("集装箱号"); }
                        if (cbgrossWeight.Checked == false)
                        { dt.Columns.Remove("毛重"); }
                        if ( cbgoodstype2.Checked == false)
                        { dt.Columns.Remove("货物种类"); }
                        dt.AcceptChanges();
                    #endregion
                    }
                }
            }
            return dt;
        }

        #region 自动完成填写公司
        [System.Web.Services.WebMethod]
        [System.Web.Script.Services.ScriptMethod]
        public static string[] GetCustomersName(string prefixText, int count)
        {
            CustomerManage cusManage = new CustomerManage();
            List<string> strArray = new List<string>();
            List<Customer> cusList = cusManage.GetModelList("");
            foreach (Customer aCus in cusList)
            {
                strArray.Add(aCus.CustomerName);
            }
            return (from m in strArray
                    where m.Contains(prefixText)
                    select m).Take(count).ToArray();
        }
        #endregion
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnQuery_Click(object sender, EventArgs e)
        {
            if (tbQuery.Text != "壳牌（中国）有限公司")
            {
                string sql = " ";
                string strFeetype = "";
                string strDate = "";
                dt = DT(sql, strFeetype, strDate);
                gvMonth.DataSource = dt;
                gvMonth.DataBind();
            }
            if (tbQuery.Text == "壳牌（中国）有限公司")
            {
                string sql = " ";
                string strFeetype = "";
                string strDate = "";
                dt = DT_QiaoPai (sql, strFeetype, strDate);
                gvMonth.DataSource = dt;
                gvMonth.DataBind();
            }

        }




        protected void gvMonth_RowDataBound(object sender, GridViewRowEventArgs e)
        {

            //int colNum = e.Row.Cells.Count;
            //e.Row.Cells[6].Visible = false;//让第7列不可见
            if (tbQuery.Text != "壳牌（中国）有限公司")
            {
                if (e.Row.RowType == DataControlRowType.Footer)
                {
                    double sum = 0.000;
                    e.Row.Cells[Convert.ToInt32(e.Row.Cells.Count) - 2].Text = "合计";
                    foreach (GridViewRow dr in gvMonth.Rows)//进行合计计算
                    {
                        sum = sum + Convert.ToDouble(dr.Cells[Convert.ToInt32(e.Row.Cells.Count) - 1].Text);
                    }
                    e.Row.Cells[Convert.ToInt32(e.Row.Cells.Count) - 1].Text = sum.ToString();
                }
            }
            if (tbQuery.Text == "壳牌（中国）有限公司")
            {
                if (e.Row.RowType == DataControlRowType.Footer)
                {
                    double sum = 0.000;
                    e.Row.Cells[Convert.ToInt32(e.Row.Cells.Count) - 2].Text = "合计";
                    foreach (GridViewRow dr in gvMonth.Rows)//进行合计计算
                    {
                        sum = sum + Convert.ToDouble(dr.Cells[Convert.ToInt32(e.Row.Cells.Count) - 3].Text);
                    }
                    e.Row.Cells[Convert.ToInt32(e.Row.Cells.Count) - 1].Text = sum.ToString();
                }
            }

        }

        #region 已禁用,原来是区分内部外部的，现在不区分
        ///// <summary>
        ///// 导出结算单
        ///// </summary>
        ///// <param name="sender"></param>
        ///// <param name="e"></param>
        //protected void btnDrive1_Click(object sender, EventArgs e)
        //{
        //    string QueryText = tbQuery.Text.Trim();
        //    if (QueryText == null || QueryText == "")
        //    { MessageBox.Show(this, "请填写客户名称！"); }
        //    else
        //    {
        //        string sql = "";
        //        string strFeetype = " and b.feetype=1 ";
        //        string strDate = "";
        //        dt = DT(sql, strFeetype, strDate);
        //        dt.Columns.Remove(dt.Columns["委托单号"]);
        //        string[] info = new string[] { tbQuery.Text };
        //        //形成EXCEL
        //        billDataToExcel datatoExcel = new billDataToExcel();
        //        BillManage dll = new BillManage();
        //        string filepath = Server.MapPath("../document/Excel/");
        //        string file = datatoExcel.OutputExceli(dt, "结算单", filepath, info);
        //        //下载文件
        //        DownloadFile(file);
        //    }
        //}
        #endregion


        /// <summary>
        /// 导出费用
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnDrive2_Click(object sender, EventArgs e)
        {
            //string QueryText = tbQuery.Text.Trim();
            //if (QueryText == null || QueryText == "")
            //{ MessageBox.Show(this, "请填写客户名称！"); }
            //else
            //{
            //    string strFeetype = " and feetype=0 ";
            //    string sql = "";
            //    string strDate = "";

            //    dt = DT(sql, strFeetype, strDate);
            //    int index = gvMonth.FooterRow.Cells.Count;
            //    DataRow dr = dt.NewRow();
            //    dr["合计"] = gvMonth.FooterRow.Cells[index - 1].Text;
            //    dt.Rows.Add(dr);
            //    billDataToExcel datatoExcel = new billDataToExcel();
            //    BillManage dll = new BillManage();
            //    string filepath = Server.MapPath("../document/Excel/");

            //    dt.Columns.Remove(dt.Columns["委托单号"]);
            //    #region 获取客户资料并导出EXCEL
            //    CustomsSystem.Model.Customer customer = new CustomsSystem.BLL.CustomerManage().GetModel(tbQuery.Text.Trim());//获取客户公司信息
            //    if (customer == null)
            //    {
            //        MessageBox.Show(this, "查无该公司，请查看公司名称是否正确！");
            //    }
            //    else
            //    { //客户公司信息
            //        string lbTel = "", lbFax = "", lbt = "", lbF = "", lbBank = "", lbBankNo = "", lbCN = "", lbfr = "";
            //        CustomsSystem.Model.Cu_Bank bank = new CustomsSystem.BLL.BankManage().GetModel(customer.ID);
            //        if (bank != null)
            //        {
            //            lbTel = bank.Phone;
            //            lbFax = bank.Fax;
            //        }
            //        //本公司信息
            //        int c = 67;
            //        CustomsSystem.Model.Cu_Bank ub = new CustomsSystem.BLL.BankManage().GetModel(c);
            //        CustomsSystem.Model.Customer cu = new CustomsSystem.BLL.CustomerManage().GetModel(c);
            //        //这里要修改
            //        if (ub != null)
            //        {
            //            lbt = ub.Phone;
            //            lbF = ub.Fax;
            //            lbBank = ub.Bank;
            //            lbBankNo = ub.BankNO;
            //        }
            //        if (customer != null)
            //        { lbCN = customer.CustomerName; }
            //        if (cu != null)
            //        { lbfr = cu.CustomerName; }

            //        string lbtime = DateTime.Now.ToShortDateString();

            //        string[] info = new string[] { lbCN, lbTel, lbFax, lbfr, lbt, lbF, lbtime, lbBank, lbBankNo };
            //        string file = datatoExcel.OutputExcelo(dt, "费用清单", filepath, info);
            //        DownloadFile(file);
            //    }
            //    #endregion
            //}
            YueJie();

        }


        //// <summary>
        /// 下载文件
        /// </summary>
        /// <param name="filename">文件物理地址</param>
        protected void DownloadFile(string file)
        {
            string filename = "/document/Excel/" + file;
            int intStart = filename.LastIndexOf(@"\") + 1;
            string saveFileName = filename.Substring(intStart, filename.Length - intStart);

            System.IO.FileInfo fi = new System.IO.FileInfo(filename);
            string fileextname = fi.Extension;
            string DEFAULT_CONTENT_TYPE = "application/unknown";
            RegistryKey regkey, fileextkey;
            string filecontenttype;
            try
            {
                regkey = Registry.ClassesRoot;
                fileextkey = regkey.OpenSubKey(fileextname);
                filecontenttype = fileextkey.GetValue("Content Type", DEFAULT_CONTENT_TYPE).ToString();
            }
            catch
            {
                filecontenttype = DEFAULT_CONTENT_TYPE;
            }
            Response.Clear();
            Response.Charset = "gb2312";
            Response.Buffer = true;
            this.EnableViewState = false;
            Response.ContentEncoding = System.Text.Encoding.Default;
            //对文件进行编码，防止下载名出现乱码
            string encodefileName = System.Web.HttpUtility.UrlEncode(file, System.Text.Encoding.UTF8);

            Response.AppendHeader("Content-Disposition", "attachment;filename=" + encodefileName);
            Response.ContentType = filecontenttype;

            Response.WriteFile(filename);
            Response.Flush();
            Response.Close();

            Response.End();

        }

        protected void gvMonth_SelectedIndexChanged(object sender, EventArgs e)
        {
            //当表行数小于1时，不允许导出EXCEL
            if (gvMonth.Rows.Count < 1)
            {
                btnDrive2.Enabled = false;
                btnticket.Enabled = false;
            }
            else btnDrive2.Enabled = true;
            btnticket.Enabled = true;
        }


        /// <summary>
        /// 导出票结
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnticket_Click1(object sender, EventArgs e)
        {
            DataSet excelDs = new DataSet();
            string QueryText = tbQuery.Text.Trim();
            string sheetid = "";
            string date = "";
            #region 日期搜索的判断
            if (tbTimestart.Text.Trim() != "")
            {
                //日期截止时间为空的时候，默认为现在的时间
                if (tbTimestop.Text.Trim() == "")
                {
                    DateTime timeStop = DateTime.Now;
                    date += " and agencyDate between '" + Convert.ToDateTime(tbTimestart.Text).ToString("yyyy-MM-dd") + "' and '" + timeStop + "'";
                }
                else
                {
                    date += " and agencyDate between '" + Convert.ToDateTime(tbTimestart.Text).ToString("yyyy-MM-dd") + "' and '" + Convert.ToDateTime(tbTimestop.Text).ToString("yyyy-MM-dd") + "'";
                }

            }
            #endregion
            //记录页面的CheckBox的状态
            for (int i = 0; i < gvMonth.Rows.Count; i++)
            {
                CheckBox ck = (CheckBox)gvMonth.Rows[i].Cells[0].FindControl("Cbbill");
                if (ck.Checked)
                {
                    sheetid += gvMonth.Rows[i].Cells[1].Text.Trim() + ",";

                }
            }

            if (sheetid.Trim() != "")
            {

                sheetid = sheetid.Substring(0, sheetid.Length - 1);
                if (ddl_Model.SelectedValue == "0")//默认导出模式
                {
                    excelDs = bll.GetTicketList(sheetid);
                    //生成excel
                    billDataToExcel datatoExcel = new billDataToExcel();
                    string FilePath = Server.MapPath("~/document/Excel/");
                    string file = datatoExcel.OutputExcelTicket(excelDs, "费用清单", FilePath, QueryText);
                    DownloadFile(file);
                }
                else//模板导出
                {
                    CustomsSystem.Model.info_formMaster fmModel = new CustomsSystem.Model.info_formMaster();
                    int id = Convert.ToInt32(ddl_Model.SelectedValue);//模板的ID
                    fmModel = fmbll.GetModel(id);
                    string name = fmModel.ExcelPath;//excel的名字
                    string path = Server.MapPath("~/document/ExcelForm/" + name);//模板Excel的地址
                    string ExcelName = fmModel.formName + Convert.ToDateTime(tbTimestop.Text).ToString("yyyyMM") + ".xls";//新的Excel名
                    string newpath = Server.MapPath("~/document/Excel/" + ExcelName);//新的Excel的名字
                    CopyDir(path, newpath);//复制Excel 
                    //DelDir(newpath);//删除过期文件
                    fBll.Exesql(id, sheetid, newpath, date);//获取sql的值
                    FormDownloadFile(newpath);//下载文件

                }
            }
            else  //如果没选中就提示
            {
                MessageBox.Show(this, "请勾选要导出excel的行");
            }
        }
        /// <summary>
        ///  全选checkbox
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void cbAll_CheckedChanged(object sender, EventArgs e)
        {

            CheckBox cball = (CheckBox)sender;//全选checkbox
            foreach (GridViewRow gr in gvMonth.Rows)//全选按钮
            {
                CheckBox sb = (CheckBox)gr.Cells[0].FindControl("Cbbill");//当前行的checkbox
                sb.Checked = cball.Checked;
            }
        }
        protected void YueJie()
        {
            if (ddl_Yue.SelectedValue == "1")
            {
                DataTable dtnew = new DataTable();
                string QueryText = tbQuery.Text.Trim();
                if (QueryText == null || QueryText == "")
                { MessageBox.Show(this, "请填写客户名称！"); }
                else
                {
                    string strFeetype = " and feetype=0 ";
                    string sql = "";
                    string strDate = "";

                    dt = DT(sql, strFeetype, strDate);
                    //记录页面的CheckBox的状态

                    DataTable dtcb = new DataTable();

                    foreach (DataColumn dc in dt.Columns )//获取列名
                    {
                        dtcb.Columns.Add(dc.ColumnName, dc.DataType);
                    }
                    for (int i = 0; i < gvMonth.Rows.Count; i++)
                    {
                        DataRow drow = dtcb.NewRow();
                        CheckBox ck = (CheckBox)gvMonth.Rows[i].Cells[0].FindControl("Cbbill");
                        if (ck.Checked == true)
                        {
                            for (int c = 0; c < dt.Columns.Count; c++)
                            {

                                drow[c] = dt.Rows[i][c];

                            }
                            dtcb.Rows.Add(drow);//加行
                        }
                    }
                    if (dtcb.Rows.Count > 0)
                    {
                        int index = gvMonth.FooterRow.Cells.Count;
                        DataRow dr = dtcb.NewRow();
                        dr["合计"] = gvMonth.FooterRow.Cells[index - 1].Text;
                        //
                        double  sum = 0;
                        int lastC = dtcb.Columns.Count - 1;
                    
                        for (int i = 0; i < dtcb.Rows .Count; i++)
                        {
                            sum +=Convert .ToDouble ( dtcb.Rows[i][lastC].ToString ());
                        }

                    
                        dtcb.Rows.Add(dr);
                        int lastR = dtcb.Rows.Count - 1;
                        dtcb.Rows[lastR][lastC] = sum;
                        dtcb.AcceptChanges();

                        billDataToExcel datatoExcel = new billDataToExcel();
                        BillManage dll = new BillManage();
                        string filepath = Server.MapPath("../document/Excel/");

                        dtcb.Columns.Remove(dtcb.Columns["委托单号"]);
                        //int index = gvMonth.FooterRow.Cells.Count;
                        //DataRow dr = dt.NewRow();
                        //dr["合计"] = gvMonth.FooterRow.Cells[index - 1].Text;
                        //dt.Rows.Add(dr);
                        //billDataToExcel datatoExcel = new billDataToExcel();
                        //BillManage dll = new BillManage();
                        //string filepath = Server.MapPath("../document/Excel/");

                        //dt.Columns.Remove(dt.Columns["委托单号"]);
                        #region 获取客户资料并导出EXCEL
                        CustomsSystem.Model.Customer customer = new CustomsSystem.BLL.CustomerManage().GetModel(tbQuery.Text.Trim());//获取客户公司信息
                        if (customer == null)
                        {
                            MessageBox.Show(this, "查无该公司，请查看公司名称是否正确！");
                        }
                        else
                        { //客户公司信息
                            string lbTel = "", lbFax = "", lbt = "", lbF = "", lbBank = "", lbBankNo = "", lbCN = "", lbfr = "";
                            CustomsSystem.Model.Cu_Bank bank = new CustomsSystem.BLL.BankManage().GetModel(customer.ID);
                            if (bank != null)
                            {
                                lbTel = bank.Phone;
                                lbFax = bank.Fax;
                            }
                            //本公司信息
                            int c = 67;
                            CustomsSystem.Model.Cu_Bank ub = new CustomsSystem.BLL.BankManage().GetModel(c);
                            CustomsSystem.Model.Customer cu = new CustomsSystem.BLL.CustomerManage().GetModel(c);
                            //这里要修改
                            if (ub != null)
                            {
                                lbt = ub.Phone;
                                lbF = ub.Fax;
                                lbBank = ub.Bank;
                                lbBankNo = ub.BankNO;
                            }
                            if (customer != null)
                            { lbCN = customer.CustomerName; }
                            if (cu != null)
                            { lbfr = cu.CustomerName; }

                            string lbtime = DateTime.Now.ToShortDateString();

                            string[] info = new string[] { lbCN, lbTel, lbFax, lbfr, lbt, lbF, lbtime, lbBank, lbBankNo };
                            string file = datatoExcel.OutputExcelo(dtcb, "费用清单", filepath, info);
                            DownloadFile(file);
                        }
                        #endregion
                    }
                    else
                    {
                        MessageBox.Show(this, "请勾选要导出excel的行");
                    }
                }
            }
            if (ddl_Yue.SelectedValue == "2")
            {
                string QueryText = tbQuery.Text.Trim();
                if (QueryText == null || QueryText == "")
                { MessageBox.Show(this, "请填写客户名称！"); }
                else
                {
                    string strFeetype = " and feetype=0 ";
                    string sql = "";
                    string strDate = "";

                    dt = DT_QiaoPai(sql, strFeetype, strDate);
                    //记录页面的CheckBox的状态

                    DataTable dtcb = new DataTable();

                    foreach (DataColumn dc in dt.Columns)//获取列名
                    {
                        dtcb.Columns.Add(dc.ColumnName, dc.DataType);
                    }
                    for (int i = 0; i < gvMonth.Rows.Count; i++)
                    {
                        DataRow drow = dtcb.NewRow();
                        CheckBox ck = (CheckBox)gvMonth.Rows[i].Cells[0].FindControl("Cbbill");
                        if (ck.Checked == true)
                        {
                            for (int c = 0; c < dt.Columns.Count; c++)
                            {

                                drow[c] = dt.Rows[i][c];

                            }
                            dtcb.Rows.Add(drow);//加行
                        }
                    }
                    if (dtcb.Rows.Count > 0)
                    {
                        int index = gvMonth.FooterRow.Cells.Count;
                        DataRow dr = dtcb.NewRow();
                        dr["合计"] = gvMonth.FooterRow.Cells[index - 1].Text;
                        //
                        double sum = 0;
                        int lastC = dtcb.Columns.Count - 1;

                        for (int i = 0; i < dtcb.Rows.Count; i++)
                        {
                            sum += Convert.ToDouble(dtcb.Rows[i][lastC].ToString());
                        }


                        dtcb.Rows.Add(dr);
                        int lastR = dtcb.Rows.Count - 1;
                        dtcb.Rows[lastR][lastC] = sum;
                        dtcb.AcceptChanges();
                        //int index = gvMonth.FooterRow.Cells.Count;
                        //DataRow dr = dt.NewRow();
                        //dr["合计"] = gvMonth.FooterRow.Cells[index - 1].Text;
                        //dt.Rows.Add(dr);
                        billDataToExcel datatoExcel = new billDataToExcel();
                        BillManage dll = new BillManage();
                        string filepath = Server.MapPath("../document/Excel/");

                        dt.Columns.Remove(dt.Columns["委托单号"]);
                        #region 获取客户资料并导出EXCEL
                        CustomsSystem.Model.Customer customer = new CustomsSystem.BLL.CustomerManage().GetModel(tbQuery.Text.Trim());//获取客户公司信息
                        if (customer == null)
                        {
                            MessageBox.Show(this, "查无该公司，请查看公司名称是否正确！");
                        }
                        else
                        { //客户公司信息
                            string lbTel = "", lbFax = "", lbt = "", lbF = "", lbBank = "", lbBankNo = "", lbCN = "", lbfr = "";
                            CustomsSystem.Model.Cu_Bank bank = new CustomsSystem.BLL.BankManage().GetModel(customer.ID);
                            if (bank != null)
                            {
                                lbTel = bank.Phone;
                                lbFax = bank.Fax;
                            }
                            //本公司信息
                            int c = 67;
                            CustomsSystem.Model.Cu_Bank ub = new CustomsSystem.BLL.BankManage().GetModel(c);
                            CustomsSystem.Model.Customer cu = new CustomsSystem.BLL.CustomerManage().GetModel(c);
                            //这里要修改
                            if (ub != null)
                            {
                                lbt = ub.Phone;
                                lbF = ub.Fax;
                                lbBank = ub.Bank;
                                lbBankNo = ub.BankNO;
                            }
                            if (customer != null)
                            { lbCN = customer.CustomerName; }
                            if (cu != null)
                            { lbfr = cu.CustomerName; }

                            string lbtime = DateTime.Now.ToShortDateString();

                            string[] info = new string[] { lbCN, lbTel, lbFax, lbfr, lbt, lbF, lbtime, lbBank, lbBankNo };
                            string file = datatoExcel.OutputExcelo_QiaoPai(dt, "货代业务对账单", filepath, info);
                            DownloadFile(file);
                        }
                        #endregion
                    }


                    else
                    {
                        MessageBox.Show(this, "请勾选要导出excel的行");
                    }
                }

            }
        }
        /// <summary>
        /// 获取DATATABLE
        /// </summary>
        /// <param name="sql">查询条件</param>
        /// <param name="strFeetype">费用类型</param>
        /// <param name="strDate">日期</param>
        /// <returns></returns>
        protected DataTable DT_QiaoPai(string sql, string strFeetype, string strDate)
        {
            string QueryText = tbQuery.Text.Trim();
            string agencyDate = "";//传入日期，在数据库是agencyDate
            if (QueryText == null || QueryText == "")
            { MessageBox.Show(this, "请填写客户名称！"); }
            else
            {
                ///验证公司
                CustomsSystem.Model.Customer cusmodel = new CustomsSystem.BLL.CustomerManage().GetModel(QueryText);
                if (cusmodel == null)
                { MessageBox.Show(this, "查无该公司，请查看公司名称是否正确！"); }

                else
                {
                    #region 当公司不为空时
                    //查询
                    //sql = " and payedID != 1 and cID = " + cusmodel.ID;
                    string cid = cusmodel.ID.ToString();
                    #region 日期搜索的判断
                    if (tbTimestart.Text.Trim() != "")
                    {
                        //日期截止时间为空的时候，默认为现在的时间
                        if (tbTimestop.Text.Trim() == "")
                        {
                            DateTime timeStop = DateTime.Now;
                            //strDate += " and 日期 between ''" + Convert.ToDateTime(tbTimestart.Text).ToString("yyyy-MM-dd") + "'' and ''" + timeStop + "''";
                            agencyDate += " and agencyDate between '" + Convert.ToDateTime(tbTimestart.Text).ToString("yyyy-MM-dd") + "' and '" + timeStop + "'";
                        }
                        else
                        {
                            //strDate += " and 日期 between ''" + Convert.ToDateTime(tbTimestart.Text).ToString("yyyy-MM-dd") + "'' and ''" + Convert.ToDateTime(tbTimestop.Text).ToString("yyyy-MM-dd") + "''";
                            agencyDate += " and agencyDate between '" + Convert.ToDateTime(tbTimestart.Text).ToString("yyyy-MM-dd") + "' and '" + Convert.ToDateTime(tbTimestop.Text).ToString("yyyy-MM-dd") + "'";
                        }

                    }
                    #endregion
                    strDate += " order by 进口日期 " + ddlorder.SelectedValue + " ";
                    //重新绑定数据库
                    CustomsSystem.BLL.BillManage bll = new BillManage();
                    dt = bll.GetPivot_QiaoPai(strFeetype, agencyDate, cid, strDate);
                    if (dt.Rows.Count < 1)
                    { //Response.Write("<script>alert('数据为空');windows.history.go(-2);</script>");
                        MessageBox.Show(this, "数据为空");
                    }
                    else
                    {
                        //去除不需要的
                        //dt.Columns.Remove("remark");
                        cb7.Visible = false;
                        cb3.Visible = false;
                        cb5.Visible = false;
                        cb9.Visible = false;
                        cb8.Visible = false;
                        cbagency.Visible = false;
                        cbcontainerNo.Visible = false;
                        cbDangerousGoods.Visible = false;
                        cbgrossWeight.Visible = false;
                        if (cb1.Checked == false)
                        { dt.Columns.Remove("订单号"); }
                        if (cb2.Checked == false)
                        { dt.Columns.Remove("合同号"); }
                        if (cb3.Checked == false)
                        { dt.Columns.Remove("船名"); }
                        if (cb4.Checked == false)
                        { dt.Columns.Remove("货名"); }
                        if (cb5.Checked == false)
                        { dt.Columns.Remove("目的地"); }
                        if (cb6.Checked == false)
                        { dt.Columns.Remove("柜号/柜型"); }
                        if (cb7.Checked == false)
                        { dt.Columns.Remove("报关单号"); }
                        if (cb8.Checked == false)
                        { dt.Columns.Remove("报检号"); }
                        if (cb9.Checked == false)
                        { dt.Columns.Remove("提单号"); }
                        dt.AcceptChanges();
                    #endregion
                    }
                }
            }
            return dt;
        }
        protected void cb_toExcel()
        {
            DataTable dtcb = new DataTable();

            foreach (DataColumn dc in  gvMonth .Columns )//获取列名
            {
                dtcb.Columns.Add(dc.ColumnName, dc.DataType);
            }
            //记录页面的CheckBox的状态


            for (int i = 0; i < gvMonth.Rows.Count; i++)
            {
                DataRow drow = dtcb.NewRow();
                CheckBox ck = (CheckBox)gvMonth.Rows[i].Cells[0].FindControl("Cbbill");
                if (ck.Checked == true)
                {
                    for (int c = 0; c < dt.Columns.Count; c++)
                    {

                        //drow[c] = gvMonth.Rows[i][c];

                    }
                }
            }
            
        }
    }
    

}